create proc [dbo].[p_AverageWeeklyConnectionCount](
	@CountOfWeeks int, 
	@DatabaseID bigint) AS  

set nocount on

declare @weeks table(
		dtWeekDate datetime,
		iWeek int,
		iYear int,
		iTotalSessions int default 0,
		iTotalConnections int default 0,
		iDBConnections int default 0)   
 
declare @iCurWeek int,  		
	@dtWeekBeginning datetime,
	@dtWeekEnding datetime,
	@iTotalSessions int,   
	@iTotalConnections int,    
	@iDBConnections int    

set @iCurWeek=0  
set @dtWeekBeginning=dbo.WeekBeginning(current_timestamp)
while @iCurWeek<@CountOfWeeks  
begin  	
	set @dtWeekEnding=dbo.WeekEnding(@dtWeekBeginning)
	
	insert into @weeks(
		dtWeekDate,
		iWeek,
		iYear,
		iTotalSessions,
		iTotalConnections,
		iDBConnections)
	select
		@dtWeekBeginning,
		datepart(ww,@dtWeekBeginning),
		datepart(year,@dtWeekBeginning),
		sum(sessioncount),
		sum(serverconnections),
		sum(databaseconnections)
	from
		connectsummary a
	where
		a.databaseid=@databaseid
		and dbo.MakeDate(a.[Year],a.[Month],a.[Day],a.[Hour],0,0) between @dtWeekBeginning and @dtWeekEnding

	set @dtWeekBeginning=dateadd(dd,-7,@dtWeekBeginning)   	
	set @iCurWeek=@iCurWeek+1  
end   

select   	
	a.dtWeekDate,
	isnull(convert(decimal(18,6),a.iTotalConnections)/convert(decimal(18,6),a.iTotalSessions),0) 'AvgCountOfAllConnections',
	isnull(convert(decimal(18,6),a.iDBConnections)/convert(decimal(18,6),a.iTotalSessions),0) 'AvgCountOfDBConnections'
from   	
	@weeks a  	  
order by   	
	a.dtWeekDate    
   
